﻿/*********************************************************
 * 开发人员：小兵
 * 创建时间：2012-10-9 15:15:51
 * 描述说明：
 * 
 * 更改历史：
 * 
 * *******************************************************/
namespace Mozlite.Data.SqlServer
{
    using System;
    using System.Web;
    using System.Data;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.SqlClient;

    /// <summary>
    /// MSSQL数据库辅助类。
    /// </summary>
    public class SqlServerHelper : SqlHelper<SqlParameter>
    {
        /// <summary>
        /// 初始化类<see cref="SqlServerHelper"/>。
        /// </summary>
        /// <param name="connectionString">链接字符串。</param>
        public SqlServerHelper(string connectionString)
            : base(connectionString)
        {
            installer = new SqlServerFactory(this);
        }
        /// <summary>
        /// 创建参数对象。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="parameterName">参数名称。</param>
        /// <param name="value">参数值。</param>
        /// <returns>返回参数对象实例。</returns>
        public override IParameter CreateParameter(string parameterName, object value)
        {
            return new SqlServerParameter(parameterName, value ?? DBNull.Value);
        }


        /// <summary>
        /// 启动事务处理对象实例。
        /// </summary>
        /// <return>当前事务处理实例对象。</return>
        public override IDbTransaction BeginTransaction()
        {
            var connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            connection.Open();
            return connection.BeginTransaction();
        }

        /// <summary>
        /// 执行单值返回方法。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回执行后的结果。</returns>
        protected override object ExecuteScalar(IDbTransaction transaction, CommandType commandType, string commandText, SqlParameter[] parameters)
        {
            var connection = GetConnection(transaction);
            var command = new SqlCommand();
            PrepareCommand(command, connection, null, commandType, commandText, parameters);
            var value = command.ExecuteScalar();
            command.Parameters.Clear();
            if(transaction == null)
                connection.Close();
            return value;
        }

        /// <summary>
        /// 执行不返回值得查询语句。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>
        /// 返回影响的行数。
        /// </returns>
        protected override int ExecuteNonQuery(IDbTransaction transaction, CommandType commandType, string commandText, SqlParameter[] parameters)
        {
            var connection = GetConnection(transaction);
            var command = new SqlCommand();
            PrepareCommand(command, connection, null, commandType, commandText, parameters);
            int rowAffected = command.ExecuteNonQuery();
            command.Parameters.Clear();
            if(transaction == null)
                connection.Close();
            return rowAffected;
        }

        /// <summary>
        /// 执行查询语句。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>
        /// 返回数据库读取对象实例。
        /// </returns>
        protected override IDataReader ExecuteReader(IDbTransaction transaction, CommandType commandType, string commandText, SqlParameter[] parameters)
        {
            var connection = GetConnection(transaction);
            var command = new SqlCommand();
            try
            {
                PrepareCommand(command, connection, null, commandType, commandText, parameters);
                IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Parameters.Clear();
                return reader;
            }
            catch
            {
                if(transaction == null)
                    connection.Close();
                throw;
            }
        }

        private SqlConnection GetConnection(IDbTransaction transaction)
        {
            if(transaction == null)
                return new SqlConnection(ConnectionString);
            return (SqlConnection)transaction.Connection;
        }

        private void PrepareCommand(SqlCommand cmd, SqlConnection conn, IDbTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if(conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if(trans != null)
                cmd.Transaction = (SqlTransaction)trans;

            cmd.CommandType = cmdType;

            if(cmdParms != null)
            {
                foreach(var parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        /// <summary>
        /// 返回分页SQL语句字符串。
        /// </summary>
        /// <param name="pageIndex">页码，从1开始。</param>
        /// <param name="pageSize">每页显示记录数。</param>
        /// <param name="fields">选择列。</param>
        /// <param name="tbWheres">FROM语句后ORDER BY之前的语句。</param>
        /// <param name="orderby">ORDER BY语句。</param>
        /// <returns>返回组合后的分页查询语句。</returns>
        protected override string ApplyPageCommandText(int pageIndex, int pageSize, string fields, string tbWheres, string orderby)
        {
            return "SELECT * FROM (SELECT ROW_NUMBER() OVER({0}) AS p_a_g_e_r_row_number,{1} FROM {2}) D WHERE D.p_a_g_e_r_row_number BETWEEN {3} AND {4};\r\nSELECT COUNT(1) FROM {2};".FormatWith(
                orderby, fields, tbWheres, (pageIndex - 1) * pageSize, pageIndex * pageSize);
        }
    }
}
